package com.xy.servicemanage.request;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.sql.BLOB;
import oracle.sql.CLOB;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sun.org.apache.xml.internal.security.utils.Base64;
import com.xy.servicemanage.db.PoolManager;
import com.xy.servicemanage.model.ServiceSetting;
import com.xy.servicemanage.model.Tdatabase;
import com.xy.servicemanage.model.Treqparams;
import com.xy.servicemanage.model.Tresparams;
import com.xy.servicemanage.model.Tservices;

public class ServiceEnginer {
	private static final Logger log = LoggerFactory
			.getLogger(ServiceEnginer.class);
	private static ServiceEnginer m_instance = new ServiceEnginer();
	private static String sqltype_insert = "update";

	private static String sqltype_query = "query";

	private static String pageNo = "PageNo";
	private static int pageSize = 5;
	private static String Total = "total";
	private static String List = "List";

	private ServiceEnginer() {
		// TODO Auto-generated constructor stub
	}

	public static ServiceEnginer getInstance() {
		return m_instance;
	}

	public Object process(Tservices service, Map values) throws Exception {

		Object ret = null;
		String sql = service.getExecSql();
		List<Treqparams> reqs = ServiceSetting.getInstance().getReqparamBySid(
				service.getSid());
		if (reqs == null)
			reqs = new ArrayList<Treqparams>();

		if ("1".equals(service.getIsDynamicSql())) {
			sql = genDynamicSql(sql, values, service);
			reqs = getDynamicPara(values, service);
		}
		String sql1 = sql;
		String dbtype = ServiceSetting.getInstance()
				.getDataBaseConfig(service.getDataSourceID()).getDbtype();
		if ("1".equals(service.getIsPage())) {
			sql = getPageSql(dbtype, sql, values);
		}
		log.debug("sql:" + sql);
		Connection con = PoolManager.getConnection(service.getDataSourceID());
		if (con == null) {
			throw new SQLException("No available connection, try it later.");
		}
		PreparedStatement ps = con.prepareStatement(sql);

		for (int i = 0; i < reqs.size(); i++) {
			Object v = values.get(reqs.get(i).getReqid());
			if ("date".equals(reqs.get(i).getReqtype()) && v != null) {
				java.util.Date d = new SimpleDateFormat(reqs.get(i)
						.getReqformat()).parse(v.toString());
				v = new java.sql.Date(d.getTime());
				// v=new java.sql.Date(
			}
			ps.setObject(i + 1, v);
			log.debug("para:" + reqs.get(i).getReqid() + ", value:" + v);
		}
		if ("1".equals(service.getIsPage())) {
			List<Integer> l = getPageParam(dbtype, values);
			int ind = reqs.size();
			ps.setInt(ind + 1, l.get(0));
			ps.setInt(ind + 2, l.get(1));
		}
		if (service.getSqlType().equals(sqltype_insert)) {
			// 更新操作
			int r = ps.executeUpdate();
			Map m = new HashMap();
			List<Tresparams> resList = ServiceSetting.getInstance()
					.getResparamBySid(service.getSid());
			m.put(resList.get(0).getResid(), r);
			List<Map> l = new ArrayList<Map>();
			l.add(m);
			ret = l;

		} else if (service.getSqlType().equals(sqltype_query)) {
			// 查询
			ResultSet rs = ps.executeQuery();
			List<Tresparams> resList = ServiceSetting.getInstance()
					.getResparamBySid(service.getSid());
			if ("1".equals(service.getReturnSingle())) {
				// 单行
				ret = querySingle(rs, resList, ServiceSetting.getInstance()
						.getDataBaseConfig(service.getDataSourceID()));
			} else if ("1".equals(service.getReturnList())) {
				// 返回多条数据
				ret = queryList(rs, resList, ServiceSetting.getInstance()
						.getDataBaseConfig(service.getDataSourceID()));
				if ("1".equals(service.getReturnTotal())) {
					// 需要返回总数
					rs.close();
					ps.close();
					int total = queryTotal(sql1, con, service, values, reqs,
							dbtype);
					Map m = new HashMap();
					m.put(Total, total);
					m.put(List, ret);
					List<Map> l = new ArrayList<Map>();
					l.add(m);
					ret = l;

				}

			} else if ("1".equals(service.getReturnCustomize())) {
				// List:{a,b,c,List:{d,f,List:{g,h}}}

			}
			if (rs != null)
				rs.close();
		}
		if (ps != null)
			ps.close();
		if (con != null)
			con.close();
		LoggerUtils.log(values);
		return ret;
	}

	private int queryTotal(String sql, Connection con, Tservices service,
			Map<String, String> values, List<Treqparams> reqs, String dbtype)
			throws Exception {
		String s;
		if ("mysql".equals(dbtype)) {
			s = "select count(*) from (" + sql + ") as total";
		} else {
			s = "select count(*) from (" + sql + ")";
		}
		PreparedStatement ps = con.prepareStatement(s);

		for (int i = 0; i < reqs.size(); i++) {
			ps.setObject(i + 1, values.get(reqs.get(i).getReqid()));
		}
		ResultSet rs = ps.executeQuery();
		rs.next();
		int r = rs.getInt(1);
		rs.close();
		ps.close();
		return r;
	}

	private List<Map<String, Object>> queryList(ResultSet rs,
			List<Tresparams> list, Tdatabase db) throws Exception {
		List<Map<String, Object>> l = new ArrayList<Map<String, Object>>();
		while (rs.next()) {
			Map<String, Object> ret = new HashMap<String, Object>();
			for (Tresparams r : list) {
				String name = r.getResid();
				ret.put(name, getValue(rs, name, db, r.getSequence(), r));
			}
			l.add(ret);
		}
		return l;
	}

	private Object querySingle(ResultSet rs, List<Tresparams> list, Tdatabase db)
			throws Exception {
		Map<String, Object> ret = new HashMap<String, Object>();
		if (rs.next()) {
			for (Tresparams r : list) {
				String name = r.getResid();
				ret.put(name, getValue(rs, name, db, r.getSequence(), r));
			}
		}
		List<Map> l = new ArrayList<Map>();
		l.add(ret);
		return l;
	}

	private Object getValue(ResultSet rs, String name, Tdatabase db, int index,
			Tresparams r) throws Exception {
		// ResultSet 序号从1开始取数据
		Object value = null;
		String typename = rs.getMetaData().getColumnTypeName(index);
		value = rs.getObject(index);
		if ("byte".equals(r.getRestype()) && value != null) {
			if (value instanceof BLOB) {
				BLOB blob = (BLOB) value;
				byte[] bytes = blobToBytes(blob);
				value = Base64.encode(bytes);
			}

		} else if ("CLOB".equals(typename)) {
			CLOB clob = (CLOB) value;
			value= clobToString(clob);
		}

		if (db.getCharset() != null) {
			// todo:字符集转换
		}
		if (value == null)
			value = "";
		return value;
	}

	private List<Treqparams> getDynamicPara(Map<String, String> values,
			Tservices service) {
		List<Treqparams> list = ServiceSetting.getInstance().getReqparamBySid(
				service.getSid());
		List<Treqparams> ret = new ArrayList<Treqparams>();
		for (Treqparams r : list) {
			if (values.get(r.getReqid()) != null
					&& !values.get(r.getReqid()).equals("")) {
				ret.add(r);
			}
		}
		return ret;

	}

	private String genDynamicSql(String sql, Map<String, String> values,
			Tservices service) {
		List<Treqparams> list = ServiceSetting.getInstance().getReqparamBySid(
				service.getSid());
		// select * from t where a=? and b=? and c=?
		List<String> del = new ArrayList<String>();
		for (Treqparams r : list) {
			if (values.get(r.getReqid()) == null
					|| values.get(r.getReqid()).equals("")) {
				del.add(r.getReqid());
			}
		}
		String tmp = sql;
		for (String p : del) {
			tmp = removeParam(tmp, p);
		}
		return tmp;
	}

	private String removeParam(String sql, String p) {
		// select a,b,c from t where a=? and b=? and c=?
		String s = sql.toLowerCase();
		String p1 = p.toLowerCase();
		int where_index = s.indexOf("where");
		String s0 = s.substring(0, where_index + 5);// select a,b,c from t where
		String s0_1 = s.substring(where_index + 5);// a=? and b=? and c=?
		int start = s0_1.indexOf(p1);

		// String s1=s0_1.substring(0,start); //select * from t where
		String s2 = s0_1.substring(start + p.length());// =? and b=? and c=?
		s2 = s2.substring(s2.indexOf("?") + 1).trim();// and b=? and c=?
		if (s2.toLowerCase().startsWith("and")) {
			s2 = s2.substring(3);
		}
		String newsql = (s0 + s2).trim();
		if (newsql.toLowerCase().endsWith("and")) {
			newsql = newsql.substring(0, newsql.length() - 3);
		}
		if (newsql.toLowerCase().endsWith("where")) {
			newsql = newsql.substring(0, newsql.length() - 5);
		}
		return newsql;
	}

	private List<Integer> getPageParam(String dbtype, Map<String, String> values) {
		List<Integer> list = new ArrayList<Integer>();
		int page = values.get(pageNo) == null ? 1 : Integer.valueOf(values
				.get(pageNo));
		int start = (page - 1) * pageSize;
		int end = page * pageSize + 1;
		if ("mysql".equals(dbtype)) {
			list.add(start);
			list.add(end);
		} else {
			list.add(end);
			list.add(start);
		}
		return list;
	}

	private String getPageSql(String dbtype, String sql,
			Map<String, String> values) {
		int page = values.get(pageNo) == null ? 1 : Integer.valueOf(values
				.get(pageNo));
		int start = (page - 1) * pageSize;
		int end = page * pageSize + 1;
		String s = "";
		if ("mysql".equals(dbtype)) {
			s = sql + " limit ?, ? ";
		} else {
			s = "select * from(select t.*,rownum rn from( " + sql
					+ ") t where rownum<?  ) where rn>? ";

		}

		return s;

	}

	private byte[] blobToBytes(BLOB blob) {
		InputStream is = null;
		byte[] b = null;
		try {
			is = blob.getBinaryStream();
			b = new byte[(int) blob.length()];
			is.read(b);
			return b;
		} catch (Exception e) {
			log.error("blobToBytes",e);
		} finally {
			try {
				is.close();
				is = null;
			} catch (IOException e) {
				log.error("blobToBytes",e);
			}
		}
		return b;
	}

	private String clobToString(CLOB clob) {
		try {
			String data = "";
			if (clob != null) {
				Reader is = clob.getCharacterStream();
				StringBuffer sb = new StringBuffer();
				int length = (int) clob.length();
				if (length > 0) {
					char[] buffer = new char[length];
					int count = 0;
					try {
						while ((count = is.read(buffer)) != -1)
							sb.append(buffer);
						data = new String(sb);
					} catch (Exception e) {
					}
				} else {
					data = (String) null;
				}
				try {
					is.close();
				} catch (IOException e) {
					log.error("clobToString",e);
				}

			}
			return data;
		} catch (SQLException e) {
			log.error("clobToString",e);
			return "";
		}
	}

}
